package ru.gerunn;

import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.StringWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;

import javax.mail.Folder;
import javax.mail.Message;
import javax.mail.Multipart;
import javax.mail.Part;
import javax.mail.Session;
import javax.mail.Store;
import javax.mail.internet.MimeBodyPart;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.w3c.dom.Document;
import org.w3c.dom.Element;

public class Main {

	public static void main(String[] args) {
		Properties properties = new Properties();

		String host = "isp.aisa.ru";
		String username = "soap@aisa.ru";
		String password = "chfndrewjir";
		String provider = "pop3";
		String filename = "yaromiravto.xls";

		try {
			Session session = Session.getDefaultInstance(properties, null);
			Store store = session.getStore(provider);
			store.connect(host, username, password);

			Folder inbox = store.getFolder("INBOX");

			if (inbox == null) {
				System.out.println("No INBOX");
			} else {
				inbox.open(Folder.READ_ONLY);
				Message[] messages = inbox.getMessages();

				boolean found = false;
				int i = messages.length - 1;
				//i = 175;

				Part part = null;

				while (i >= 0 && !found) {
					Message message = messages[i];
					System.out.println(message.getMessageNumber());
					System.out.println(message.getSubject());
					Object content = message.getContent();

					if (content instanceof Multipart) {
						Multipart multipart = (Multipart) content;

						for (int j = 0; j < multipart.getCount(); j++) {
							part = multipart.getBodyPart(j);
							String disposition = part.getDisposition();

							// System.out.println(disposition);

							if (disposition == null) {
							} else {
								if (disposition.toLowerCase().equals(Part.ATTACHMENT) || disposition.toLowerCase().equals(Part.INLINE)) {
									MimeBodyPart mimeBodyPart = (MimeBodyPart) part;

									if (mimeBodyPart.isMimeType("text/plain")) {
										// System.out.println("text/plain");
									} else {
										if (part.getFileName().equals(filename)) {
											// System.out.println("found!");
											found = true;
											break;
										}
									}
								}
							}
						}
					}

					// System.in.read();

					i--;
				}

				if (part != null) {
					// System.out.println(part.getFileName());
					// System.out.println(part.getContentType());
					// System.out.println(part.getContent());

					File saveFile = new File(filename);

					BufferedOutputStream bos = new BufferedOutputStream(new FileOutputStream(saveFile));

					byte[] buff = new byte[2048];
					InputStream is = part.getInputStream();
					int ret = 0;
					// int count = 0;
					while ((ret = is.read(buff)) > 0) {
						bos.write(buff, 0, ret);
						// count += ret;
					}
					bos.close();
					is.close();

					Workbook workbook = WorkbookFactory.create(part.getInputStream());
					Sheet sheet = workbook.getSheetAt(0);

					DocumentBuilderFactory docFactory = DocumentBuilderFactory.newInstance();
					DocumentBuilder docBuilder = docFactory.newDocumentBuilder();

					Document doc = docBuilder.newDocument();
					Element rootElement = doc.createElement("yaromiravto");
					doc.appendChild(rootElement);

					// Decide which rows to process
					// int rowStart = sheet.getFirstRowNum();
					int rowStart = 1;
					int rowEnd = sheet.getLastRowNum();

					for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
						Row r = sheet.getRow(rowNum);
						if (r != null) {
							Cell modelCell = r.getCell(0, Row.RETURN_BLANK_AS_NULL);
							Cell packCell = r.getCell(1, Row.RETURN_BLANK_AS_NULL);
							Cell descrCell = r.getCell(2, Row.RETURN_BLANK_AS_NULL);
							Cell engineCell = r.getCell(3, Row.RETURN_BLANK_AS_NULL);
							Cell colorCell = r.getCell(4, Row.RETURN_BLANK_AS_NULL);
							Cell colorCodeCell = r.getCell(5, Row.RETURN_BLANK_AS_NULL);
							Cell insideCell = r.getCell(6, Row.RETURN_BLANK_AS_NULL);
							Cell addsCell = r.getCell(7, Row.RETURN_BLANK_AS_NULL);
							Cell statusCell = r.getCell(8, Row.RETURN_BLANK_AS_NULL);
							Cell priceCell = r.getCell(9, Row.RETURN_BLANK_AS_NULL);

							if (modelCell == null) {
							} else {
								System.out.println(
									modelCell.toString()
									+ "\t" + priceCell.toString()
								);

								Element itemElement = doc.createElement("item");

								Element modelElement = doc.createElement("model");
								modelElement.appendChild(doc.createTextNode(modelCell.toString()));
								itemElement.appendChild(modelElement);

								Element packElement = doc.createElement("pack");
								packElement.appendChild(doc.createTextNode(packCell == null ? "" : packCell.toString()));
								itemElement.appendChild(packElement);

								Element descrElement = doc.createElement("descr");
								descrElement.appendChild(doc.createTextNode(descrCell == null ? "" : descrCell.toString()));
								itemElement.appendChild(descrElement);

								Element engineElement = doc.createElement("engine");
								engineElement.appendChild(doc.createTextNode(engineCell == null ? "" : engineCell.toString()));
								itemElement.appendChild(engineElement);

								Element colorElement = doc.createElement("color");
								colorElement.appendChild(doc.createTextNode(colorCell == null ? "" : colorCell.toString()));
								itemElement.appendChild(colorElement);

								Element colorCodeElement = doc.createElement("colorcode");
								colorCodeElement.appendChild(doc.createTextNode(colorCodeCell == null ? "" : colorCodeCell.toString()));
								itemElement.appendChild(colorCodeElement);

								Element insideElement = doc.createElement("inside");
								insideElement.appendChild(doc.createTextNode(insideCell == null ? "" : insideCell.toString()));
								itemElement.appendChild(insideElement);

								Element addsElement = doc.createElement("adds");
								addsElement.appendChild(doc.createTextNode(addsCell == null ? "" : addsCell.toString().replaceAll("[\r\n]", "")));
								itemElement.appendChild(addsElement);

								Float statusFloat = Float.parseFloat(statusCell == null ? "0" : statusCell.toString());
								Element statusElement = doc.createElement("status");
								statusElement.appendChild(doc.createTextNode(statusFloat.intValue() + ""));
								itemElement.appendChild(statusElement);

								Float priceFloat = Float.parseFloat(priceCell == null ? "0" : priceCell.toString());
								Element priceElement = doc.createElement("price");
								priceElement.appendChild(doc.createTextNode(priceFloat.intValue() + ""));
								itemElement.appendChild(priceElement);

								rootElement.appendChild(itemElement);
							}
						}

					}

					TransformerFactory transformerFactory = TransformerFactory.newInstance();
					Transformer transformer = transformerFactory.newTransformer();
					DOMSource source = new DOMSource(doc);
					StringWriter stringWriter = new StringWriter();

					// StreamResult result = new StreamResult(new
					// File("test.xml"));
					// transformer.transform(source, result);

					transformer.transform(source, new StreamResult(stringWriter));

					// System.out.println(stringWriter.toString());

					String result = stringWriter.toString();

					// *
					if (result != null) {
						try {
							DAO dao = new DAO();

							Connection conn = dao.getConnection("jdbc:db2://195.170.201.6:50000/yaromir", "shl", "bublik");

							PreparedStatement ps = conn.prepareStatement("update shl.wwwblocks set data = ? where id = ?");
							// ps.setBlob(1, new
							// ByteArrayInputStream(result.getBytes()));
							ps.setBytes(1, result.getBytes());
							ps.setInt(2, 22122);
							ps.executeUpdate();
							ps.close();

							conn.close();
						} catch (SQLException e) {
							e.printStackTrace();
							while (e != null) {
								System.err.println("Error msg: " + e.getMessage());
								System.err.println("SQLSTATE: " + e.getSQLState());
								System.err.println("Error code: " + e.getErrorCode());
								e.printStackTrace();
								e = e.getNextException(); // For drivers that
															// support chained
															// exceptions
							}
						}
					}
					// */

				}

				inbox.close(false);
				store.close();
			}
		} catch (Exception exception) {
			exception.printStackTrace();
		}
	}

}
